﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_CopyTeam]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_CopyTeam];
GO
CREATE PROCEDURE [dbo].[sproc_CopyTeam]
    @FromTeamID int,
    @ToTeamID int,
    @Operator nvarchar(50)
/*

============================================================
功能:    Copy组
参数:
    @FromTeamID int    :    被copy的源组（类）ID
    @ToTeamID int    :    copy到的目标组（类）ID
    @Operator    :    操作人

============================================================

*/
AS
--判断ToTeamID是否是FromTeamID的子ID
DECLARE @id     int
DECLARE @pid     int

--在使用临时表时加上，否则无法正确返回记录
SET NOCOUNT ON

SET @pid=@ToTeamID
SET @id =0

--得到所有被访问组的父组
CREATE TABLE #pteam (pid int)
INSERT INTO #pteam VALUES(@pid)
WHILE (@pid<>@id)
BEGIN
    SET @id = @pid
    SELECT @pid = classparentid FROM uds_class WHERE classid = @id
    INSERT INTO #pteam VALUES(@id)    
End

IF EXISTS(SELECT 1 FROM #pteam WHERE pid = @FromTeamID)
BEGIN
    PRINT '不能复制到子项目中去~~'
    RETURN -1
END
--生成树表
create table #subclass(id int,pid int,nid int)

DECLARE @ids nvarchar(2000)
DECLARE @tmpids nvarchar(2000)
DECLARE @oldids nvarchar(2000)
DECLARE @sql nvarchar(2000)
DECLARE @row int
DECLARE @tmprow int

--初始化变量
SET @ids =''
SET @tmpids=''
SET @oldids =''
SET @sql = ''
SET @row = 1
SET @tmprow = 1

SET @oldids = @FromTeamID



PRINT '----'
WHILE (@row)>0
    BEGIN
        --插入本节点
        INSERT INTO #subclass VALUES(@FromTeamID,0,0)

        SET @sql = 'insert into #subclass select classid,classparentid,0 from uds_class where classparentid in (' + @oldids +') AND  classparentid <>classid'
        PRINT @sql
        EXEC (@sql)
        
        SET @tmprow = @@ROWCOUNT

        SET @tmpids=''
        SELECT @tmpids = @tmpids + CONVERT(nvarchar,id) + ',' FROM #subclass
        
        IF LEN(@tmpids)>0
            set @tmpids = LEFT(@tmpids,LEN(@tmpids)-1)

        SET @oldids = @tmpids

        IF(@row<>@tmprow)
        BEGIN
            DELETE FROM #subclass
            SET @row = @tmprow
        END 
        ELSE
            SET @row = 0
        
    END 
PRINT '---'
SELECT * FROM #subclass
--添加父节点
INSERT INTO uds_class(
    ClassName,
    ClassRemark,
    ClassType,
    ClassParentID,
    ClassAddedBy,
    ClassAddedDate,
    Status
    )
    SELECT  UDS_CLASS.ClassName,
        UDS_CLASS.ClassRemark,
        UDS_CLASS.ClassType,
        @ToTeamID,
        @Operator,
        GetDate(),
        0
    FROM UDS_CLASS WHERE CLASSID=@FromTeamID
--更新父节点的nid
    UPDATE #subclass SET nid=@@identity WHERE id=@FromTeamID

--插入子节点
DECLARE @name nvarchar(100)
DECLARE @remark nvarchar(500)
DECLARE @type int
DECLARE @npid int

SET @pid = @FromTeamID
WHILE(EXISTS (
    SELECT  1
    FROM #subclass WHERE (nid is null OR nid = 0 )
    
))
BEGIN
    SELECT  
        @id = UDS_CLASS.ClassID,
        @name = UDS_CLASS.ClassName,
        @remark = UDS_CLASS.ClassRemark,
        @type = UDS_CLASS.ClassType,
        @pid = UDS_CLASS.ClassParentID
    FROM UDS_CLASS
    WHERE ClassID IN (
        SELECT TOP 1 id 
        FROM #subclass 
        WHERE (nid is null OR nid = 0) 
        )

--找到父节点的新的节点id
    SELECT @npid = nid FROM #subclass WHERE id = @pid
    INSERT INTO uds_class(
    ClassName,
    ClassRemark,
    ClassType,
    ClassParentID,
    ClassAddedBy,
    ClassAddedDate,
    Status
    )
    VALUES(
    @name,
    @remark,
    @type,
    @npid,
    @Operator,
    GetDate(),
    0)

    UPDATE #subclass SET nid = @@identity WHERE id = @id
    

END
    
    RETURN 0

SET NOCOUNT OFF